﻿<%@ WebHandler Language="C#" Class="CheckPointResultReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using ReportBusiness;

/**
 * Description: 检测项查询报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2023.9.8
 **/

public class CheckPointResultReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getSearchSqlString(JObject jObject)
    {
        string sql = @"
SELECT cci.mfgordername,NVL(cci2.containername,cci.containername) containername,cci.originalstartdate,f.factoryname,cid.createdate instoredate,
       DECODE(cci.isinstore,-1,'领料中',0,'在制',1,'完工入库') isinstore,
       ws.workflowstepname,
       spi.stepno || '-' || spi.description stepinfoname,
       '' stepinfotype,
       sc.bubblenumber,
       sc.checkpointnotedis_b,sc.drawingrequiredis_b,
       wk.fullname worker,scr.createdate workerdate,scr.workcheckpointresultnotedis_b,
       ck.fullname checker,scr.checkdate,scr.checkpointresultnotedis_b,
       cci.familylistnames,cci.productname,cci.productdescription,
       wf.part_revid,pt.producttypename,
       wf.process_no,wf.ly2sequence,
       CASE WHEN wfb.workflowname IS NOT NULL THEN wfb.workflowname || ':(' || wf.workflowrevision || ')'
            ELSE '' 
       END workflowinfo,
       wf.workflowtype,wf.workflowtype2,
       srp.zbz,srp.zfpdbh,
       '' zlwtczd,cmm.zbhgdh,
       DECODE(sc.meopcheck,1,'是','否') meopcheck,
       DECODE(sc.keycheck,1,'是','否') keycheck,
       DECODE(sc.qualitycheck,1,'是','否') qualitycheck,
       DECODE(sc.isinstall,1,'是','否') isinstall,
       DECODE(sc.isdisassemble,1,'是','否') isdisassemble,
       DECODE(sc.isnotice,1,'是','否') isnotice,
       DECODE(sc.isreturn,1,'是','否') isreturn,
       DECODE(sc.iscompp,1,'是','否') iscompp,
       DECODE(sc.isspc,1,'是','否') isspc,
       sc.spctype,sc.spcrule
FROM stepcheckpoint sc
LEFT JOIN stepcheckpointresult scr ON scr.stepcheckpointid = sc.stepcheckpointid
LEFT JOIN workflowstep ws ON ws.workflowstepid = sc.workflowstepid
LEFT JOIN stepinfo spi ON spi.stepinfoid = sc.stepinfoid
INNER JOIN containercurrentinfo cci ON cci.containerid = scr.containerid
LEFT JOIN container cci2 ON cci2.containerid = scr.childcontainerid
LEFT JOIN containersteprecord csr ON csr.containersteprecordid = scr.containerstepworkreportid
LEFT JOIN workflow wf ON wf.workflowid = csr.workflowid
LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid
LEFT JOIN employee wk ON wk.employeeid = scr.workerid
LEFT JOIN employee ck ON ck.employeeid = scr.checkerid
LEFT JOIN product p ON p.productid = cci.productid
LEFT JOIN producttype pt ON pt.producttypeid = p.producttypeid
LEFT JOIN containerstepscrapinfo srp ON srp.containerid = cci.containerid AND srp.workflowstepid = ws.workflowstepid
LEFT JOIN containernonconforminginfo cmm ON cmm.containerid = cci.containerid AND cmm.workflowstepid = ws.workflowstepid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN containerinstore cid ON cid.charg = cci.containername
WHERE 1=1 ";
        //主制分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }

        //订单号
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        //零件号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        //批次号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        //工序名称
        if (jObject["workflowstepname"] != null && jObject["workflowstepname"].ToString() != "")
        {
            sql += string.Format(" AND ws.workflowstepname LIKE '%{0}%'", jObject["workflowstepname"].ToString());
        }

        //批次在制状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            sql += string.Format(" AND cci.isinstore = '{0}'", jObject["status"].ToString());
        }

        //机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND cci.familylistnames = '{0}'", jObject["productfamilyname"].ToString());
        }

        //开卡时间
        if (jObject["containerstartdate"] != null && jObject["containerstartdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["containerstartdate"].ToString());
            sql += string.Format(" AND TRUNC(cci.originalstartdate) >= TO_DATE('{0}','yyyy-mm-dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(cci.originalstartdate) <= TO_DATE('{0}','yyyy-mm-dd')", arr[1].ToString());
        }

        //入库时间
        if (jObject["instoredate"] != null && jObject["instoredate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["instoredate"].ToString());
            sql += string.Format(" AND TRUNC(cid.createdate) >= TO_DATE('{0}','yyyy-mm-dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(cid.createdate) <= TO_DATE('{0}','yyyy-mm-dd')", arr[1].ToString());
        }

        //检验录入时间
        if (jObject["checkdate"] != null && jObject["checkdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["checkdate"].ToString());
            sql += string.Format(" AND TRUNC(scr.checkdate) >= TO_DATE('{0}','yyyy-mm-dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(scr.checkdate) <= TO_DATE('{0}','yyyy-mm-dd')", arr[1].ToString());
        }
        
        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns["containername"].Caption = "批次号";
        dtResult.Columns["originalstartdate"].Caption = "开卡时间";
        dtResult.Columns["factoryname"].Caption = "主制分厂";
        dtResult.Columns["instoredate"].Caption = "入库时间";
        dtResult.Columns["isinstore"].Caption = "批次状态";
        dtResult.Columns["stepinfoname"].Caption = "工步名称";
        dtResult.Columns["stepinfotype"].Caption = "工步类型";
        dtResult.Columns["bubblenumber"].Caption = "检验序号";
        dtResult.Columns["checkpointnotedis_b"].Caption = "检验内容描述";
        dtResult.Columns["drawingrequiredis_b"].Caption = "图纸规定";
        dtResult.Columns["workcheckpointresultnotedis_b"].Caption = "实测值内容--工人";
        dtResult.Columns["worker"].Caption = "录入人--工人";
        dtResult.Columns["workerdate"].Caption = "录入时间--工人";
        dtResult.Columns["checkpointresultnotedis_b"].Caption = "实测值内容--检验员";
        dtResult.Columns["checker"].Caption = "录入人--检验员";
        dtResult.Columns["checkdate"].Caption = "录入时间--检验员";
        dtResult.Columns["mfgordername"].Caption = "订单号";
        dtResult.Columns["familylistnames"].Caption = "机型";
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["part_revid"].Caption = "设计图版次";
        dtResult.Columns["producttypename"].Caption = "件号类型";
        dtResult.Columns["process_no"].Caption = "工艺规程编号";
        dtResult.Columns["ly2sequence"].Caption = "工艺规程版次";
        dtResult.Columns["workflowinfo"].Caption = "工艺路线";
        dtResult.Columns["workflowtype"].Caption = "工艺类型";
        dtResult.Columns["workflowtype2"].Caption = "工艺类型2";
        dtResult.Columns["zbz"].Caption = "备注";
        dtResult.Columns["zfpdbh"].Caption = "废品单编号";
        dtResult.Columns["zlwtczd"].Caption = "质量问题处置单";
        dtResult.Columns["zbhgdh"].Caption = "不合格品审理单";
        dtResult.Columns["meopcheck"].Caption = "工序检";
        dtResult.Columns["keycheck"].Caption = "关键过程";
        dtResult.Columns["qualitycheck"].Caption = "质保单";
        dtResult.Columns["isinstall"].Caption = "安装单";
        dtResult.Columns["isdisassemble"].Caption = "拆卸单";
        dtResult.Columns["isnotice"].Caption = "通知单";
        dtResult.Columns["isreturn"].Caption = "返回单";
        dtResult.Columns["iscompp"].Caption = "综合性能表";
        dtResult.Columns["isspc"].Caption = "是否SPC";
        dtResult.Columns["spctype"].Caption = "SPC检测类型";
        dtResult.Columns["spcrule"].Caption = "SPC判异规则";
        dtResult.Columns["workflowstepname"].Caption = "工序名称";
        ExportHelper.ExportExcel(dtResult, "检测项检测导出");
    }
}